<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Karas_model extends MY_Model {
	
	public function __construct() {
		$this->_table = 'karas';
		parent::__construct();
	}
	
	public function search ($key, $limit_clause = "10", $vol = "") {
		if ($vol) {
			return $this->db->query("SELECT * FROM {$this->_table} WHERE vol = {$vol} $limit_clause")	;
		}
		
		$array_anphas = array ("A", "B", "C", "D", "E", "F", "G", "H", "I", "J"
				, "K", "L", "M", "N", "O", "P", "Q", "R", "S","T", "U", "W", "X",
				"Y", "Z");
		
		$sql = "SELECT * FROM {$this->_table} WHERE ";
		
		if (in_array (strtoupper($key), $array_anphas)) {
			$where = "LOWER(CONVERT(name USING utf8)) LIKE '".mb_strtolower($key,'utf-8')."%' ";
			
			switch (strtoupper($key)) {
				case 'D':
					$where .= " OR LOWER(CONVERT(name USING utf8)) LIKE '".mb_strtolower("Đ",'utf-8')."%'";
					break;	
				case 'O':
					$where .= " OR LOWER(CONVERT(name USING utf8)) LIKE '".mb_strtolower("Đ",'utf-8')."%'
					AND LOWER(CONVERT(name USING utf8)) NOT LIKE '".mb_strtolower("Đ",'utf-8')."%'
					";
					break;
			}
		} else {
			if (strlen($key) > 10) {
				$where = "
				LOWER(CONVERT(name USING utf8)) LIKE '".mb_strtolower($key,'utf-8')."%'
				or
				LOWER(CONVERT(name1 USING utf8)) LIKE '".mb_strtolower($key,'utf-8')."%'
				or
				LOWER(CONVERT(short_cont USING utf8)) LIKE '%".mb_strtolower($key,'utf-8')."%'
				or
				LOWER(CONVERT(short_cont1 USING utf8)) LIKE '%".mb_strtolower($key,'utf-8')."%'
				or
				LOWER(CONVERT(author USING utf8)) LIKE '%".mb_strtolower($key,'utf-8')."%'";
			
			} else {
				$where = "
				LOWER(CONVERT(name USING utf8)) LIKE '".mb_strtolower($key,'utf-8')."%'
				or
				LOWER(CONVERT(name1 USING utf8)) LIKE '".mb_strtolower($key,'utf-8')."%'";
			}	
		}
                $sql .= $where;
                
		$sql .= ' ORDER BY name ASC';
		
		if ($limit_clause) {
			$sql .= $limit_clause;
		}
		
		return $this->db->query($sql);
	}
	
	public function count_all_search($key, $vol = "") {
		return count($this->search($key, "", $vol)->result_array());
	}
	
	public function update_by_code($data, $code) {
		$this->db->where('code', $code);
		$this->db->update($this->_table, $data);
	}
	
    public function get_xpath_query ($arr_filter = array ()) {
        $key_uppercase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        $key_lowercase = 'abcdefghijklmnopqrstuvwxyz';

        $arr_condition = array();
        
        if (isset($arr_filter['keyword']) && !empty($arr_filter['keyword'])) {
            $keyword = strtolower($arr_filter['keyword']);
            
            if (strlen($keyword) == 1) {
                $arr_condition[] = ' startwith="'.$keyword.'"';
            } else {
                if (count(explode(' ', $keyword)) > 3) {
                    $arr_condition[] = ' (contains(translate(name1, "'.$key_uppercase.'", "'.$key_lowercase.'"), "'.$keyword.'")'
                    . ' or contains(translate(short_cont1, "'.$key_uppercase.'", "'.$key_lowercase.'"), "'.$keyword.'")'
                    . ' or contains(translate(name, "'.$key_uppercase.'", "'.$key_lowercase.'"), "'.$keyword.'")'
                    . ' or contains(translate(author, "'.$key_uppercase.'", "'.$key_lowercase.'"), "'.$keyword.'")'
                    . ' or contains(translate(short_cont, "'.$key_uppercase.'", "'.$key_lowercase.'"), "'.$keyword.'") ) ';
                } else if (count(explode(' ', $keyword)) == 1) {
                    $arr_condition[] = ' name1="'.$keyword.'"';
                } else {
                    $arr_condition[] = ' (contains(translate(name1, "'.$key_uppercase.'", "'.$key_lowercase.'"), "'.$keyword.'")'
                    . ' or contains(translate(name, "'.$key_uppercase.'", "'.$key_lowercase.'"), "'.$keyword.'"))';
                }
                
            }
        }
        
        if (isset($arr_filter['vol']) && !empty($arr_filter['vol'])) {
            $arr_condition[] = ' vol='.$arr_filter['vol'].'';
        }
        
        $xpath_query = 'song';
        if (!empty($arr_condition)) {
            $xpath_query .= '[' . implode(" and ", $arr_condition) . ']';
        }
    
        return $xpath_query;
    }
    
}